package com.xuxueli.applyModules.utils;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 */
public class CompareColumnsOralcePg {

    public static Map<String, CompareColumnsEntity> processPgSql(File file) {

        Map<String, CompareColumnsEntity> resultMap = new HashMap<>(16);
        try {
            BufferedReader br = new BufferedReader(new FileReader(file));
            String s;
            // 使用readLine方法，一次读一行
            int count = 0;
            while ((s = br.readLine()) != null) {
                s = s.toLowerCase();

                String reT = "([0-9]{2,8})";
                Pattern patT = Pattern.compile(reT);
                Matcher matT = patT.matcher(s);
                String invaluedV = "000";
                if (matT.find(0)) {
                    invaluedV = matT.group(1);
                }

                if (s.contains("create table") && !s.contains("_dcjet") && !s.contains("copy")
                    && !s.contains(invaluedV)) {

                    List<String> cArr = Stream.of(s.replaceAll("\"", "").replaceAll("\\(", "").split("\\.")).sorted()
                        .collect(Collectors.toList());
                    String tableName = cArr.get(1).replaceAll(" ", "");

                    count++;
                    do {
                        CompareColumnsEntity compareColumnsEntity = new CompareColumnsEntity();
                        s = s.toLowerCase().replaceAll("collate \"pg_catalog\"\\.\"default\"", "");
                        if (s.contains(" default ")) {
                            String[] sArray = s.split(" ");
                            String column = sArray[2].replaceAll(" ", "").replaceAll("\"", "");
                            String columTypeLength = sArray[3];

                            String type = null;
                            String length = null;

                                String reg = "([0-9]{1,3})";
                                Pattern pat = Pattern.compile(reg);
                                Matcher mat = pat.matcher(columTypeLength);
                                if (mat.find(0)) {
                                    length = mat.group(1).replace("(", "").replace(")", "");
                                    type = columTypeLength.replaceAll("\\(", "").replaceAll("\\)", "")
                                        .replaceAll(length, "");
                                }

                            String defaultV = s.split("default")[1];
                            compareColumnsEntity.setColumnsName(column);
                            compareColumnsEntity.setTableName(tableName);
                            compareColumnsEntity.setColumnsLong(length);
                            compareColumnsEntity.setColumnsType(type);
                            compareColumnsEntity.setDefaultVale(defaultV);
                            resultMap.put(tableName + "_" + column, compareColumnsEntity);
                        }
                    } while (!(s = br.readLine()).equals(";"));

                }
            }
            br.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return resultMap;
    }

    public static Map<String, CompareColumnsEntity> processOracleSql(File file) {

        Map<String, CompareColumnsEntity> resultMap = new HashMap<>(16);
        try {
            BufferedReader br = new BufferedReader(new FileReader(file));
            String s;
            // 使用readLine方法，一次读一行
            while ((s = br.readLine()) != null) {
                s = s.toLowerCase();
                if (s.contains("create table")) {
                    String[] tn = s.split("\\.");
                    String tableName = tn[1].replaceAll(" ", "");
                    do {
                        CompareColumnsEntity compareColumnsEntity = new CompareColumnsEntity();
                        s = s.toLowerCase().trim();
                        if (s.contains("default ")) {
                            String[] sArray = s.split(" ");
                            String column = sArray[0];
                            String[] sa = s.replaceAll(" ", "").replaceAll(column, "").split("default");
                            String typeF = sa[0];
                            String defaultV = sa[1].replaceAll("notnull", "").replaceAll(",", "");
                            String type = null;
                            String length = null;

                            String reg = "([0-9]{1,3})";
                            Pattern pat = Pattern.compile(reg);
                            Matcher mat = pat.matcher(typeF);
                            if (mat.find(0)) {
                                length = mat.group(1).replace("(", "").replace(")", "");
                                type = typeF.replaceAll("\\(", "").replaceAll("\\)", "").replaceAll(length, "");
                            }

                            compareColumnsEntity.setColumnsName(column);
                            compareColumnsEntity.setTableName(tableName);
                            compareColumnsEntity.setColumnsLong(length);
                            compareColumnsEntity.setColumnsType(type);
                            compareColumnsEntity.setDefaultVale(defaultV);

                            resultMap.put(tableName + "_" + column, compareColumnsEntity);
                        }
                    } while (!(s = br.readLine()).contains("tablespace"));
                }
            }
            br.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return resultMap;
    }

    public static void main(String[] args) {

        String pgPath = "D:\\downfiles\\pgsql\\stk.sql";
        String oralcePath = "D:\\downfiles\\orcle\\dcstk.sql";
        File fileOralce = new File(pgPath);
        Map<String, CompareColumnsEntity> pgResult = processPgSql(fileOralce);
        File filePg = new File(oralcePath);
        Map<String, CompareColumnsEntity> orResult = processOracleSql(filePg);

        System.out.println("表名\t字段名\t类型\t默认值\t备注");

        orResult.keySet().forEach(key -> {
            if (null == pgResult.get(key)) {
                CompareColumnsEntity entity = orResult.get(key);
                if (null != entity) {
                    System.out.println(entity.getTableName() + "\t" + entity.getColumnsName() + "\t"
                        + entity.getColumnsType() + "\t" + entity.getDefaultVale() + "\t oracle表中存在的默认值，pg表中不存在");
                }
            }
        });
        System.out.println();
        System.out.println();
        pgResult.keySet().forEach(key -> {
            if (null == orResult.get(key)) {
                CompareColumnsEntity entity = pgResult.get(key);
                if (null != entity) {
                    System.out.println(entity.getTableName() + "\t" + entity.getColumnsName() + "\t"
                        + entity.getColumnsType() + "\t" + entity.getDefaultVale() + "\t pg表中存在的默认值，oracle表中不存在");
                }
            }
        });

    }
}
